﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;
using DataTable = System.Data.DataTable;
namespace 年会考勤人员
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string url = "Server=192.168.0.223;DataBase=WEIXIN;Uid=sa;Pwd=Sy123";
            SqlConnection conn = new SqlConnection(url);
            string sql = String.Format("\tSELECT \t\t\r\n\t\t\tA.ZGBH\tPlayerNo,\r\n\t\t\tA.ZGXM\t[Name],\r\n\t\t\tB.LBMC\tTitle,\r\n\t\t\tA.LXDH\tPhoneNo\r\n\t\t\t--,A.* \r\n\r\n\t\tFROM RZ.RZERP_SYDZ.DBO.rlzy_rsdab A\r\n\t\tINNER JOIN RZ.RZERP_SYDZ.DBO.czybmlbb B ON A.BMBH = B.LBBH\r\n\t\tINNER JOIN(\r\n\t\t\tSELECT DISTINCT\t\t\t\r\n\t\t\t\tL.dwEnrollNumber\r\n\t\t\tFROM WEIXIN.DBO.t_LogData L\r\n\t\t\tWHERE 1=1\r\n\t\t\t\tAND L.dwDate > '{0}'\t\r\n\t\t)KQ\r\n\t\t\tON A.KQKH = KQ.dwEnrollNumber\r\n\t\tWHERE 1=1\r\n\t\t\tAND A.LZSJ IS NULL\r\n\t\t\tAND A.ZGZT <> '离职  '\r\n\t\tORDER BY\r\n\t\t\tA.BMBH,A.ZGBH", dateTimePicker1.Value);
            //SqlCommand cmd = new SqlCommand(sql, conn)
            DataTable data = new DataTable();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, conn);
            conn.Open();
            sqlDataAdapter.Fill(data);
            dataGridView1.DataSource = data;
            conn.Close();
            conn.Dispose();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string fileName = "";
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";
            saveDialog.FileName = fileName;
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
            {
                return;
            }
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
                return;
            }
            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            //写入标题
            for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
            {
                worksheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;
            }
            for (int r = 0; r < this.dataGridView1.Rows.Count; r++)
            {
                for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = this.dataGridView1.Rows[r].Cells[i].Value;
                }
                System.Windows.Forms.Application.DoEvents();
            }
            worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
            MessageBox.Show(fileName + "数据导出成功", "提示", MessageBoxButtons.OK);
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    workbook.SaveCopyAs(saveFileName);  //fileSaved = true; 
                }
                catch (Exception ex)
                {
                    //fileSaved = false; 
                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                }
                xlApp.Quit();
                GC.Collect();//强行销毁
            }
        }
    }
}
